package com.hhit.bigscreenbackmanage.util;


import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel导出操作工具类
 * @author jianglw
 */
public class ExcelUtils {
	/**
     * 导出Excel的多sheet页方法，可自定义列宽
     * @author: jiagnlw
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个sheet页)
     * @param sheetName （sheet的名称） 必填
     * @param title 标题
     * @param bool 是否需要合计行
     * @param title2 合计行
     * @param allRowHeight 各个行高的map有序集合
     * @param colNamesWidth 表头的名称和宽度map有序集合
     * @param colKeysStyle 列的样式map集合 (目前包含 double2[保留两位小数],double[保留整数],date[日期格式],datetime[日期时间格式],def[默认])
     * @param    listMap 数据源
     * @throws Exception
     */

	public static void exportExcel2(HSSFWorkbook workbook, int sheetNum,
                                    String sheetName, String title,
                                    Boolean bool, String title2,
                                    Map<String, Short> allRowHeight,
                                    Map<String,Integer> colNamesWidth,
                                    Map<String, String> colKeysStyle,
                                    List<Map> listMap, HttpServletRequest request, HttpServletResponse response) throws IOException {
        // 生成一个表格  
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetName);
        // 设置表格默认列宽度为20个字节  
        int colWidthIndex = 0;
        for (String str : colNamesWidth.keySet()) {
        	sheet.setColumnWidth(colWidthIndex, (int)(colNamesWidth.get(str).intValue() + 0.72) * 256);
        	colWidthIndex++;
		}
        int index = 0;
        //************************样式MAP**********************************************
        
        Map<String, CellStyle> cellStyleMap = new HashMap<String, CellStyle>();
        //标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        	Font font = workbook.createFont();
        	font.setColor(HSSFColor.BLACK.index);
        	font.setFontHeightInPoints((short) 16);  
        	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleStyle.setFont(font);  
        titleStyle.setWrapText(true);
        cellStyleMap.put("title", titleStyle);
        //合计行样式
        CellStyle totalStyle2 = workbook.createCellStyle();
        totalStyle2.setFillForegroundColor(HSSFColor.WHITE.index);
        totalStyle2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        totalStyle2.setAlignment(CellStyle.ALIGN_LEFT);
        	Font font2 = workbook.createFont();
        	font2.setColor(HSSFColor.BLACK.index);
        	font2.setFontHeightInPoints((short) 12);  
        	font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        totalStyle2.setFont(font2);  
        totalStyle2.setWrapText(true);
        cellStyleMap.put("total", totalStyle2);
        //表头样式
        CellStyle titleStyle3 = workbook.createCellStyle();
        titleStyle3.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle3.setAlignment(CellStyle.ALIGN_CENTER);
        	Font font3 = workbook.createFont();
        	font3.setColor(HSSFColor.BLACK.index);
        	font3.setFontHeightInPoints((short) 12);  
        	font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleStyle3.setFont(font3);  
        titleStyle3.setWrapText(true);
        cellStyleMap.put("col", titleStyle3);
        //保留3位小数
        CellStyle styleDouble3 = workbook.createCellStyle();
        //垂直居中
        styleDouble3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble3.setAlignment(CellStyle.ALIGN_RIGHT);
        HSSFDataFormat format= workbook.createDataFormat();
        styleDouble3.setDataFormat(format.getFormat("0.000"));
        cellStyleMap.put("double3", styleDouble3);
        //保留2位小数
        CellStyle styleDouble2 = workbook.createCellStyle();
        //垂直居中
		styleDouble2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styleDouble2.setAlignment(CellStyle.ALIGN_RIGHT);
		styleDouble2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		cellStyleMap.put("double2", styleDouble2);
		//保留整数
		CellStyle styleDouble = workbook.createCellStyle();
        //垂直居中
		styleDouble.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styleDouble.setAlignment(CellStyle.ALIGN_RIGHT);
		styleDouble.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
		cellStyleMap.put("double", styleDouble);
		//时间格式
		CellStyle styleDateTime = workbook.createCellStyle();
        //垂直居中
		styleDateTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styleDateTime.setAlignment(CellStyle.ALIGN_CENTER);
		CreationHelper time = workbook.getCreationHelper();
		styleDateTime.setDataFormat(time.createDataFormat().getFormat("yyyy-MM-dd  hh:mm:ss"));
		cellStyleMap.put("datetime", styleDateTime);
		//日期格式
		CellStyle styleDate = workbook.createCellStyle();
        //垂直居中
		styleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styleDate.setAlignment(CellStyle.ALIGN_CENTER);
		styleDate.setDataFormat(time.createDataFormat().getFormat("yyyy-MM-dd"));
		cellStyleMap.put("date", styleDate);
		//默认格式
		CellStyle styleDef = workbook.createCellStyle();
        //垂直居中
		styleDef.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //居左
		styleDef.setAlignment(CellStyle.ALIGN_LEFT);
		styleDef.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
		cellStyleMap.put("def", styleDef);
        CellStyle styleDefs = workbook.createCellStyle();
        //垂直居中
        styleDefs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //居左
        styleDefs.setAlignment(CellStyle.ALIGN_LEFT);
        styleDefs.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
        cellStyleMap.put("defs", styleDefs);
		//默认居中
		CellStyle styleCenter = workbook.createCellStyle();
        //垂直居中
		styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //居中
		styleCenter.setAlignment(CellStyle.ALIGN_CENTER);
		styleCenter.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
		cellStyleMap.put("center", styleCenter);
		//**********************************************************************
        // -----------------------第一行标题
        Row row = sheet.createRow(index);
        row.setHeight(allRowHeight.get("titleHeight"));
        Cell cell = row.createCell((short) 0);
        cell.setCellStyle(titleStyle);
        cell.setCellValue(title);
        CellRangeAddress cra=new CellRangeAddress(0, 0, 0, colNamesWidth.size()-1);
        sheet.addMergedRegion(cra);
        index++;
        //------------------------第二行合计
        if(bool){
	        Row row2 = sheet.createRow(index);
	        row2.setHeight(allRowHeight.get("totalHeight"));
	        Cell cell2 = row2.createCell((short) 0);
	        cell2.setCellStyle(totalStyle2);
	        cell2.setCellValue(title2);
	        CellRangeAddress cra2=new CellRangeAddress(1, 1, 0, colNamesWidth.size()-1);
	        sheet.addMergedRegion(cra2);
	        index++;
        }
        //------------------------第三行表头
        Row row3 = sheet.createRow(index);
        row3.setHeight(allRowHeight.get("colHeight"));
        Cell cell3 = null;
        int colIndex = 0;
        RichTextString text = null;
        for (String colName: colNamesWidth.keySet()) {  
        	cell3 = row3.createCell((short) colIndex);  
            cell3.setCellStyle(titleStyle3);  
            text = new HSSFRichTextString(colName);
            cell3.setCellValue(text.toString());
            colIndex++;
        }
        index++;
        //冻结第二行
        sheet.createFreezePane(0, index, 0, index);
        
        //--------------------------------正文格式---------------------------------
        // 遍历集合数据，产生数据行
        if (listMap != null) {
            for (Map m : listMap) {
                row = sheet.createRow(index);
                row.setHeight(allRowHeight.get("rowHeight"));
                int cellIndex = 0;
                for (String key : colKeysStyle.keySet()) {
					Cell cellN = row.createCell((short) cellIndex);
					if((null == m.get(key) || "".equals(m.get(key)))&& "a".equals(key)){
                    	cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }else if(null == m.get(key) || "".equals(m.get(key)))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                   else if((null == m.get(key) || "".equals(m.get(key)))&& "b".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "c".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "d".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "e".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "f".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else{
                    	if("double".equals(colKeysStyle.get(key))){
							cellN.setCellValue(Integer.parseInt(m.get(key).toString()));
                    	}else if("double2".equals(colKeysStyle.get(key))){
                            cellN.setCellValue(Double.parseDouble(m.get(key).toString()));
                        }else if("double3".equals(colKeysStyle.get(key))){
                            cellN.setCellValue(Double.parseDouble(m.get(key).toString()));
                        }else if("datetime".equals(colKeysStyle.get(key))) {
							cellN.setCellValue(DateUtils.stringToDate( String.valueOf(m.get(key)), "yyyy-MM-dd HH:mm:ss"));
						}else if("defs".equals(colKeysStyle.get(key))){
                            cellN.setCellValue("");
                        }
						else if("date".equals(colKeysStyle.get(key))){
							System.out.println("-----------------6---------------");
                    		cellN.setCellValue(DateUtils.stringToDate( String.valueOf(m.get(key))));
                    	}else{
							System.out.println("-----------------7----------------"+m.get(key).toString());
                    		cellN.setCellValue(String.valueOf(m.get(key).toString()));
                    	}
						System.out.println("-----------------8---------------"+m.get(key).toString());
                    	cellN.setCellStyle(cellStyleMap.get(String.valueOf(colKeysStyle.get(key))));
                    }
                    cellIndex++;
                }
                index++;
            }
        }else{
            System.out.println("-----------------9---------------");
		}
		// 设置response参数，可以打开下载页面
		OutputStream ouputStream = response.getOutputStream();
		try {
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/msexcel");
			String dataName = DateUtils.dateToString1(new Date(),"yyyyMMddHHmmss");
			response.setHeader("Content-disposition", "attachment;filename=" + new String(dataName.getBytes(), "ISO8859-1") + ".xls");
			workbook.write(ouputStream);
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			ouputStream.flush();
			ouputStream.close();
		}
	}

    /**
     * 导出Excel的多sheet页方法，可自定义列宽。请参照 RetailMarketRetrospectiveStatisticsController.todayListExport 方法
     * @author: weilijun
     *
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个sheet页)
     * @param sheetName （sheet的名称） 必填
     * @param rowHeighs 每一行的高度的数组(列如：表头共3行 {800(第一行高度), 600(第二行高度), 600(第三行高度), 500(循环数据行高度), 最后一个元素为循环数据的行数}
     * @param colWidths 列宽(最大列数的数组，如：表头可能有合并，但内容列取的最大的列数量)
     * @param biaoTouContentList 表头的每一行的数据以Map为元素，Map的key是String以逗号分隔的座标, value是列名
     * @param colKeysStyle 循环内容的样式map集合 (目前包含 double3[保留三位小数],double2[保留两位小数],double[保留整数],date[日期格式],datetime[日期时间格式],def[默认])
     * @param listMap 数据源
     * @throws Exception
     */
    public static void exportExcel3(HSSFWorkbook workbook, int sheetNum,
                                    String sheetName,
                                    Short rowHeighs[],
                                    Integer colWidths[],
                                    List<Map<String, String>> biaoTouContentList,
                                    Map<String, String> colKeysStyle,
                                    List<Map> listMap,
                                    HttpServletRequest request, HttpServletResponse response) throws IOException {
        // 生成一个表格
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetName);

        //样式MAP
        Map<String, CellStyle> cellStyleMap = styleMap(workbook);

        //设置表格默认列宽
        for (int i=0; i < colWidths.length; i++) {
            sheet.setColumnWidth(i, (int)(colWidths[i].intValue() + 0.72) * 256);
        }

        //设置表头(非循环的所有数据)
        Row row = null;
        CellRangeAddress cra = null;
        String keys[] = null;
        int index = 0;
        //开始循环行
        for (int i = 0; i < rowHeighs.length - 1; i++){
            row = sheet.createRow(index);
            row.setHeight(rowHeighs[i]);
            //赋值给合并或者未合并的表头单元格
            for (String key: biaoTouContentList.get(i).keySet()){
                keys = key.split(",");
                Cell cell = row.createCell((short)(Integer.parseInt(keys[2])));
                if(i == 0){
                    cell.setCellStyle(cellStyleMap.get("title"));
                }else{
                    cell.setCellStyle(cellStyleMap.get("col"));
                }
                cell.setCellValue(biaoTouContentList.get(i).get(key));
                if(!keys[0].equals(keys[1]) || !keys[2].equals(keys[3])){
                    cra = new CellRangeAddress(Integer.parseInt(keys[0]), Integer.parseInt(keys[1]), Integer.parseInt(keys[2]), Integer.parseInt(keys[3]));
                    sheet.addMergedRegion(cra);
                }
            }
            index++;
        }
        //冻结表头
        sheet.createFreezePane(0, rowHeighs.length-1, 0, rowHeighs.length-1);

        // 遍历集合数据，产生数据行
        if (listMap != null) {
            for (Map m : listMap) {
                row = sheet.createRow(index);
                row.setHeight(rowHeighs[rowHeighs.length-1]);
                int cellIndex = 0;
                for (String key : colKeysStyle.keySet()) {
                    Cell cell = row.createCell((short) cellIndex);
                    if((null == m.get(key) || "".equals(m.get(key)))){
                        cell.setCellValue("");
                        cell.setCellStyle(cellStyleMap.get("def"));
                    }else{
                        if(colKeysStyle.get(key).equals("double")){
                            cell.setCellValue(Integer.parseInt(m.get(key).toString()));
                        }else if(colKeysStyle.get(key).equals("double2")){
                            cell.setCellValue(Double.parseDouble(m.get(key).toString()));
                        }else if(colKeysStyle.get(key).equals("double3")){
                            cell.setCellValue(Double.parseDouble(m.get(key).toString()));
                        }else if("datetime".equals(colKeysStyle.get(key))) {
                            cell.setCellValue(DateUtils.stringToDate( String.valueOf(m.get(key)), "yyyy-MM-dd HH:mm:ss"));
                        }else if("date".equals(colKeysStyle.get(key))){
                            cell.setCellValue(DateUtils.stringToDate( String.valueOf(m.get(key))));
                        }else{
                            cell.setCellValue(String.valueOf(m.get(key).toString()));
                        }
                        cell.setCellStyle(cellStyleMap.get(String.valueOf(colKeysStyle.get(key))));
                    }
                    cellIndex++;
                }
                index++;
            }
        }else{
            System.out.println("-----------------9---------------");
        }
        colse(workbook, response);
    }
    /**
     * 关闭流
     *
     * @Author weiLiJun
     * @Date 2019/4/3 10:30
     * @param response
     * @return void
     */
    static void colse(Workbook workbook, HttpServletResponse response){
        // 设置response参数，可以打开下载页面
        OutputStream ouputStream = null;
        try {
            ouputStream = response.getOutputStream();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/msexcel");
            String dataName = DateUtils.dateToString1(new Date(),"yyyyMMddHHmmss");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(dataName.getBytes(), "ISO8859-1") + ".xls");
            workbook.write(ouputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                ouputStream.flush();
                ouputStream.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 循环格式的map集合
     *
     * @Author weiLiJun
     * @Date 2019/4/3 10:30
     * @param workbook
     * @return Map<String, CellStyle>
     */
    static Map<String, CellStyle> styleMap(Workbook workbook){
        Map<String, CellStyle> cellStyleMap = new HashMap<String, CellStyle>();
        //标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 16);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleStyle.setFont(font);
        titleStyle.setWrapText(true);
        cellStyleMap.put("title", titleStyle);
        //表头样式
        CellStyle titleStyle1 = workbook.createCellStyle();
        titleStyle1.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle1.setAlignment(CellStyle.ALIGN_CENTER);
        Font font1 = workbook.createFont();
        font1.setColor(HSSFColor.BLACK.index);
        font1.setFontHeightInPoints((short) 12);
        font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleStyle1.setFont(font1);
        titleStyle1.setWrapText(true);
        cellStyleMap.put("col", titleStyle1);

        //保留3位小数
        CellStyle styleDouble3 = workbook.createCellStyle();
        styleDouble3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble3.setAlignment(CellStyle.ALIGN_RIGHT);
        HSSFDataFormat format= (HSSFDataFormat)workbook.createDataFormat();
        styleDouble3.setDataFormat(format.getFormat("0.000"));
        cellStyleMap.put("double3", styleDouble3);
        //保留2位小数
        CellStyle styleDouble2 = workbook.createCellStyle();
        styleDouble2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble2.setAlignment(CellStyle.ALIGN_RIGHT);
        styleDouble2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        cellStyleMap.put("double2", styleDouble2);
        //保留整数
        CellStyle styleDouble = workbook.createCellStyle();
        styleDouble.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble.setAlignment(CellStyle.ALIGN_RIGHT);
        styleDouble.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        cellStyleMap.put("double", styleDouble);
        //时间格式
        CellStyle styleDateTime = workbook.createCellStyle();
        styleDateTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDateTime.setAlignment(CellStyle.ALIGN_CENTER);
        CreationHelper time = workbook.getCreationHelper();
        styleDateTime.setDataFormat(time.createDataFormat().getFormat("yyyy-MM-dd  hh:mm:ss"));
        cellStyleMap.put("datetime", styleDateTime);
        //日期格式
        CellStyle styleDate = workbook.createCellStyle();
        styleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDate.setAlignment(CellStyle.ALIGN_CENTER);
        styleDate.setDataFormat(time.createDataFormat().getFormat("yyyy-MM-dd"));
        cellStyleMap.put("date", styleDate);
        //默认居左
        CellStyle styleDef = workbook.createCellStyle();
        styleDef.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDef.setAlignment(CellStyle.ALIGN_LEFT);
        styleDef.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
        cellStyleMap.put("def", styleDef);
        //居中
        CellStyle styleCenter = workbook.createCellStyle();
        styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleCenter.setAlignment(CellStyle.ALIGN_CENTER);
        styleCenter.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
        cellStyleMap.put("center", styleCenter);

        return cellStyleMap;
    }

    /**
     * 导出Excel的多sheet页方法，可自定义列宽
     * @author: jiagnlw
     * @param workbook
     * @param sheetNum (sheet的位置，0表示第一个sheet页)
     * @param sheetName 表名称
     * @param bool 是否需要合计行
     * @param allRowHeight 各个行高的map有序集合
     * @param colNamesWidth 表头的名称和宽度map有序集合
     * @param colKeysStyle 列的样式map集合 (目前包含 double2[保留两位小数],double[保留整数],date[日期格式],datetime[日期时间格式],def[默认])
     * @param    listMap 数据源
     * @throws Exception
     */
    public static void exportExcel3(HSSFWorkbook workbook, int sheetNum,
                                    String sheetName, Boolean bool,
                                    Map<String, Short> allRowHeight,
                                    Map<String,Integer> colNamesWidth,
                                    Map<String, String> colKeysStyle,
                                    List<Map> listMap, HttpServletRequest request, HttpServletResponse response) throws IOException {
        // 生成一个表格
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetName);
        // 设置表格默认列宽度为20个字节
        int colWidthIndex = 0;
        for (String str : colNamesWidth.keySet()) {
            sheet.setColumnWidth(colWidthIndex, (int)(colNamesWidth.get(str).intValue() + 0.72) * 256);
            colWidthIndex++;
        }
        int index = 0;
        //************************样式MAP**********************************************

        Map<String, CellStyle> cellStyleMap = new HashMap<String, CellStyle>();
        //标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 16);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleStyle.setFont(font);
        titleStyle.setWrapText(true);
        cellStyleMap.put("title", titleStyle);
        //合计行样式
        CellStyle totalStyle2 = workbook.createCellStyle();
        totalStyle2.setFillForegroundColor(HSSFColor.WHITE.index);
        totalStyle2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        totalStyle2.setAlignment(CellStyle.ALIGN_LEFT);
        Font font2 = workbook.createFont();
        font2.setColor(HSSFColor.BLACK.index);
        font2.setFontHeightInPoints((short) 12);
        font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        totalStyle2.setFont(font2);
        totalStyle2.setWrapText(true);
        cellStyleMap.put("total", totalStyle2);
        //表头样式
        CellStyle titleStyle3 = workbook.createCellStyle();
        titleStyle3.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle3.setAlignment(CellStyle.ALIGN_CENTER);
        Font font3 = workbook.createFont();
        font3.setColor(HSSFColor.BLACK.index);
        font3.setFontHeightInPoints((short) 12);
        font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleStyle3.setFont(font3);
        titleStyle3.setWrapText(true);
        cellStyleMap.put("col", titleStyle3);
        //保留3位小数
        CellStyle styleDouble3 = workbook.createCellStyle();
        //垂直居中
        styleDouble3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble3.setAlignment(CellStyle.ALIGN_RIGHT);
        HSSFDataFormat format= workbook.createDataFormat();
        styleDouble3.setDataFormat(format.getFormat("0.00"));
        cellStyleMap.put("double3", styleDouble3);
        //保留2位小数
        CellStyle styleDouble2 = workbook.createCellStyle();
        //垂直居中
        styleDouble2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble2.setAlignment(CellStyle.ALIGN_RIGHT);
        styleDouble2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        cellStyleMap.put("double2", styleDouble2);
        //保留整数
        CellStyle styleDouble = workbook.createCellStyle();
        //垂直居中
        styleDouble.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDouble.setAlignment(CellStyle.ALIGN_RIGHT);
        styleDouble.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        cellStyleMap.put("double", styleDouble);
        //时间格式
        CellStyle styleDateTime = workbook.createCellStyle();
        //垂直居中
        styleDateTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDateTime.setAlignment(CellStyle.ALIGN_CENTER);
        CreationHelper time = workbook.getCreationHelper();
        styleDateTime.setDataFormat(time.createDataFormat().getFormat("yyyy-MM-dd  hh:mm:ss"));
        cellStyleMap.put("datetime", styleDateTime);
        //日期格式
        CellStyle styleDate = workbook.createCellStyle();
        //垂直居中
        styleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDate.setAlignment(CellStyle.ALIGN_CENTER);
        styleDate.setDataFormat(time.createDataFormat().getFormat("yyyy-MM-dd"));
        cellStyleMap.put("date", styleDate);
        //默认格式
        CellStyle styleDef = workbook.createCellStyle();
        //垂直居中
        styleDef.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //居左
        styleDef.setAlignment(CellStyle.ALIGN_LEFT);
        styleDef.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
        cellStyleMap.put("def", styleDef);
        CellStyle styleDefs = workbook.createCellStyle();
        //垂直居中
        styleDefs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //居左
        styleDefs.setAlignment(CellStyle.ALIGN_LEFT);
        styleDefs.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
        cellStyleMap.put("defs", styleDefs);
        //默认居中
        CellStyle styleCenter = workbook.createCellStyle();
        //垂直居中
        styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //居中
        styleCenter.setAlignment(CellStyle.ALIGN_CENTER);
        styleCenter.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
        cellStyleMap.put("center", styleCenter);
        //**********************************************************************
        // -----------------------第一行标题
        Row row = sheet.createRow(index);
        //------------------------第三行表头
        Row row3 = sheet.createRow(index);
        row3.setHeight(allRowHeight.get("colHeight"));
        Cell cell3 = null;
        int colIndex = 0;
        RichTextString text = null;
        for (String colName: colNamesWidth.keySet()) {
            cell3 = row3.createCell((short) colIndex);
            cell3.setCellStyle(titleStyle3);
            text = new HSSFRichTextString(colName);
            cell3.setCellValue(text.toString());
            colIndex++;
        }
        index++;
        //冻结第二行
        sheet.createFreezePane(0, index, 0, index);

        //--------------------------------正文格式---------------------------------
        // 遍历集合数据，产生数据行
        if (listMap != null) {
            for (Map m : listMap) {
                row = sheet.createRow(index);
                row.setHeight(allRowHeight.get("rowHeight"));
                int cellIndex = 0;
                for (String key : colKeysStyle.keySet()) {
                    Cell cellN = row.createCell((short) cellIndex);
                    if((null == m.get(key) || "".equals(m.get(key)))&& "a".equals(key)){
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }else if(null == m.get(key) || "".equals(m.get(key)))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "b".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "c".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "d".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "e".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else if((null == m.get(key) || "".equals(m.get(key)))&& "f".equals(key))
                    {
                        cellN.setCellValue("");
                        cellN.setCellStyle(cellStyleMap.get("def"));
                    }
                    else{
                        if("double".equals(colKeysStyle.get(key))){
                            cellN.setCellValue(Integer.parseInt(m.get(key).toString()));
                        }else if("double2".equals(colKeysStyle.get(key))){
                            cellN.setCellValue(Double.parseDouble(m.get(key).toString()));
                        }else if("double3".equals(colKeysStyle.get(key))){
                            cellN.setCellValue(Double.parseDouble(m.get(key).toString()));
                        }else if("datetime".equals(colKeysStyle.get(key))) {
                            cellN.setCellValue(DateUtils.stringToDate( String.valueOf(m.get(key)), "yyyy-MM-dd HH:mm:ss"));
                        }else if("defs".equals(colKeysStyle.get(key))){
                            cellN.setCellValue("");
                        }
                        else if("date".equals(colKeysStyle.get(key))){
                            cellN.setCellValue(DateUtils.stringToDate( String.valueOf(m.get(key))));
                        }else{
                            cellN.setCellValue(String.valueOf(m.get(key).toString()));
                        }
                        cellN.setCellStyle(cellStyleMap.get(String.valueOf(colKeysStyle.get(key))));
                    }
                    cellIndex++;
                }
                index++;
            }
        }else{
            System.out.println("-----------------9---------------");
        }
    }
    // 设置response参数，可以打开下载页面
    public static void closeOuputStream(HttpServletResponse response, HSSFWorkbook workbook, String dataName)throws IOException{
        OutputStream ouputStream = response.getOutputStream();
        try {
            response.setCharacterEncoding("GBK");
            response.setContentType("application/msexcel");
//            String dataName = DateUtils.dateToString1(new Date(),"yyyyMMddHHmmss");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(dataName.getBytes(), "ISO-8859-1") + ".xls");
            workbook.write(ouputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ouputStream.flush();
            ouputStream.close();
        }
    }
}
